﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BLL;
using Microsoft.Office.Interop.Excel;
using Model;

namespace LoginFrame
{
    public partial class FrmQueryEquipment : Form
    {
        public FrmQueryEquipment()
        {
            InitializeComponent();

        }
        private string strFileName = null;
        private DataGridView dgv = null;

        //退出
        private void toolStripLabel退出_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        //人员新增
        private void toolStripLabel添加_Click(object sender, EventArgs e)
        {
            FrmAddEquipment frm = new FrmAddEquipment();
            //frm.FormClosed += new FormClosedEventHandler(FormRefresh);
            //frm.getName += new GetName(login_getName);
            //frm.Message("AddAdmin");
            frm.ShowDialog();
        }

        private void FormRefresh(object sender, FormClosedEventArgs e)
        {
            //dataGridView1.DataSource=null;

            dataBind();
        }

        //委托获取传来的值 (刷新列表)
        public void login_getName(string s)
        {
            dataBind();
        }

        //修改
        private void toolStripLabel修改_Click(object sender, EventArgs e)
        {
            if (((System.Windows.Forms.BaseCollection)(dataGridView1.SelectedRows)).Count > 1)
            {
                MessageBox.Show("请选择一条数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            else
            {
               
                int UserID = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);
                FrmEditEquipment frm = new FrmEditEquipment(UserID,this);
                //frm.Message(UserID, "Update");
                //frm.FormClosed += new FormClosedEventHandler(FormRefresh);
                frm.ShowDialog();
            }
        }

        /// <summary>
        /// 绑定数据源
        /// </summary>
        void dataBind()
        {

            DataSet ds = bllEquipment.getAllEquipment();//查询管理员
            dataGridView1.DataSource = ds.Tables[0].DefaultView;
            dataGridView1.Columns["equipmentID"].HeaderText = "自增编号";
            dataGridView1.Columns["orderNumber"].HeaderText = "序号";
            dataGridView1.Columns["fixedAssetsType"].HeaderText = "固定资产类别";
            dataGridView1.Columns["fixedAssetsName"].HeaderText = "固定资产名称";
            dataGridView1.Columns["specification"].HeaderText = "规格型号";
            dataGridView1.Columns["unit"].HeaderText = "计量单位";
            dataGridView1.Columns["total"].HeaderText = "数量";
            dataGridView1.Columns["price"].HeaderText = "原值";
            dataGridView1.Columns["dateOfUse"].HeaderText = "使用日期";
            dataGridView1.Columns["department"].HeaderText = "部门单位";
            dataGridView1.Columns["useLocation"].HeaderText = "在用地点";

            dataGridView1.Columns["state"].HeaderText = "设备状态";
            dataGridView1.Columns["remarks"].HeaderText = "备注";
            toolStripStatusLabel2.Text = dataGridView1.Rows.Count.ToString();
        }

        private void dataGridView1_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
        {
            if (dataGridView1.SelectedRows != null)
            {
                toolStripLabel修改.Enabled = true;
                toolStripLabel删除.Enabled = true;
            }
            else
            {
                toolStripLabel修改.Enabled = true;
                toolStripLabel删除.Enabled = true;
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            string userName = textBox1.Text.Trim();
            if (userName == "")
            {
                toolStripButton1_Click_1(sender, e);
            }
            else
            {
                DataSet ds = new BU_UserInfo().SelectAdmin("U_RoleType=2 and U_Sex=0 and U_Name like '%" + userName + "%' ");
                dataGridView1.DataSource = ds.Tables[0].DefaultView;
            }
        }

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            dataBind();
        }
        //删除
        private void toolStripLabel删除_Click(object sender, EventArgs e)
        {
            if (((System.Windows.Forms.BaseCollection)(dataGridView1.SelectedRows)).Count > 1)
            {
                MessageBox.Show("请选择一条数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }
            else
            {
                string ID = dataGridView1.CurrentRow.Cells[0].Value.ToString();

                if (bllEquipment.DelEquipment(ID))
                {
                    MessageBox.Show("删除成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    FrmQueryEquipment_Load_1(sender, e);
                }
                else
                    MessageBox.Show("删除失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }

        }

       
        //显示全部
        private void toolStripButton1_Click_1(object sender, EventArgs e)
        {
            dataBind();
        }

        //窗体加载
        private void FrmQueryEquipment_Load_1(object sender, EventArgs e)
        {
            dataBind();
            this.dataGridView1.ReadOnly = true;
            //this.treeView1.Nodes[0].Expand();//展开
        }
                                                                                                                                                                                                                                                         
        private void toolStripLabelImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "表格文件 (*.xls)|*.xls;*.xlsx";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {

                strFileName = openFileDialog.FileName;
                dgv = dataGridView1;
                this.backgroundWorkerIn.RunWorkerAsync(); // 运行 backgroundWorker 组件 
                backgroundWorkerIn.WorkerReportsProgress = true;
                backgroundWorkerIn.WorkerSupportsCancellation = true;
                FrmRadProcessBar form = new FrmRadProcessBar(this.backgroundWorkerIn, "正在导入表格。。。。。");// 显示进度条窗体 
                form.ShowDialog(this);
                form.Close();

                
                //Import(openFileDialog.FileName);
            }
        }

        private bool saveExcel(DataSet ds, BackgroundWorker worker, DoWorkEventArgs e)
        {
            int i = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                i++;
                worker.ReportProgress(  (i * 100 / ds.Tables[0].Rows.Count));
                if (worker.CancellationPending)  // 如果用户取消则跳出处理数据代码 
                {
                    e.Cancel = true;
                    break;
                }
                if (i <=2)
                {
                    continue;//前两行没用
                }
                
                //foreach (DataColumn dc in ds.Tables[0].Columns)
                {
                    Equipment equipment = new Equipment();

                    //序号
                    try
                    {
                        if (dr[0].ToString() != "")
                        {
                            equipment.orderNumber = Convert.ToInt32(dr[0].ToString());  //
                        }

                    }
                    catch
                    {
                        MessageBox.Show("第" + i + "行，序号请输入正确的格式!");
                        return false;
                    }
                    //固定资产类别
                    equipment.fixedAssetsType = dr[1].ToString();

                    //固定资产名称
                    if (dr[2].ToString() == "")
                    {
                        MessageBox.Show("第" + i + "行，固定资产名称输入不能为空!");
                        return false;
                    }
                    equipment.fixedAssetsName = dr[2].ToString();

                    //规格型号
                    equipment.specification = dr[3].ToString();

                    //计量单位
                    if (dr[4].ToString() == "")
                    {
                        MessageBox.Show("第" + i + "行，计量单位输入不能为空!");
                        return false;
                    }
                    equipment.unit = dr[4].ToString();

                    //数量
                    try
                    {
                        equipment.total = Convert.ToSingle(dr[5].ToString());
                    }
                    catch
                    {
                        MessageBox.Show("第" + i + "行，数量请输入正确的格式!");
                        return false;
                    }

                    //原值
                    try
                    {
                        equipment.price = Convert.ToSingle(dr[6].ToString());  //图书价格
                    }
                    catch
                    {
                        MessageBox.Show("第" + i + "行，原值请输入正确的格式!");
                        return false;
                    }

                    //使用日期
                    try
                    {
                        equipment.dateOfUse = Convert.ToDateTime(dr[7].ToString());  //图书价格
                    }
                    catch
                    {
                        MessageBox.Show("第" + i + "行，日期请输入正确的格式!");
                        return false;
                    }

                    //部门单位
                    if (dr[8].ToString() == "")
                    {
                        MessageBox.Show("第" + i + "行，部门单位输入不能为空!");
                        return false;
                    }
                    //部门单位
                    equipment.department = dr[8].ToString();

                    //在用地点
                    equipment.useLocation = dr[9].ToString();

                    //设备状态
                    if (dr[10].ToString() == "")
                    {
                        MessageBox.Show("第" + i + "行，设备状态输入不能为空!");
                        return false;
                    }
                    equipment.state = dr[10].ToString(); //设备状态

                   
                    equipment.remarks = dr[11].ToString(); //备注
                    BLL.bllEquipment.AddEquipment(equipment);
                    
                }
            }
            return true;
        }

        /// <summary>
        /// 导入excel数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static bool Import(string filePath)
        {
            try
            {
                //Excel就好比一个数据源一般使用
                //这里可以根据判断excel文件是03的还是07的，然后写相应的连接字符串
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
                using (OleDbConnection con = new OleDbConnection(strConn))
                {
                    con.Open();
                    string[] names = GetExcelSheetNames(con);
                    if (names.Length > 0)
                    {
                        foreach (string name in names)
                        {
                            using (OleDbCommand cmd = con.CreateCommand())
                            {
                                cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName]要如此格式
                                using (OleDbDataReader odr = cmd.ExecuteReader())
                                {
                                    int i = 0;
                                    while (odr.Read())
                                    {
                                        i++;
                                        if (odr[1].ToString() == "固定资产类别")        //过滤列头  按你的实际Excel文件
                                            continue;
                                        Equipment equipment = new Equipment();


                                        //序号
                                        try
                                        {
                                            if (odr[0].ToString() != "")
                                            {
                                                equipment.orderNumber = Convert.ToInt32(odr[0].ToString());  //
                                            }
                                            
                                        }
                                        catch
                                        {
                                            MessageBox.Show("第"+i+"行，序号请输入正确的格式!");
                                            odr.Close();
                                            return false;
                                        }
                                        //固定资产类别
                                        equipment.fixedAssetsType = odr[1].ToString();

                                        //固定资产名称
                                        if (odr[2].ToString() == "")
                                        {
                                            MessageBox.Show("第" + i + "行，固定资产名称输入不能为空!");
                                            odr.Close();
                                            return false;
                                        }
                                        equipment.fixedAssetsName = odr[2].ToString();

                                        //规格型号
                                        equipment.specification = odr[3].ToString();

                                        //计量单位
                                        if (odr[4].ToString() == "")
                                        {
                                            MessageBox.Show("第" + i + "行，计量单位输入不能为空!");
                                            odr.Close();
                                            return false;
                                        }
                                        equipment.unit = odr[4].ToString(); 

                                        //数量
                                        try
                                        {
                                            equipment.total = Convert.ToInt32(odr[5].ToString());  
                                        }
                                        catch
                                        {
                                            MessageBox.Show("第" + i + "行，数量请输入正确的格式!");
                                            odr.Close();
                                            return false;
                                        }

                                        //原值
                                        try
                                        {
                                            equipment.price = Convert.ToSingle(odr[6].ToString());  //图书价格
                                        }
                                        catch
                                        {
                                            MessageBox.Show("第" + i + "行，原值请输入正确的格式!");
                                            odr.Close();
                                            return false;
                                        }

                                        //使用日期
                                        try
                                        {
                                            equipment.dateOfUse = Convert.ToDateTime(odr[7].ToString());  //图书价格
                                        }
                                        catch
                                        {
                                            MessageBox.Show("第" + i + "行，日期请输入正确的格式!");
                                            odr.Close();
                                            return false;
                                        }

                                        //部门单位
                                        if (odr[8].ToString() == "")
                                        {
                                            MessageBox.Show("第" + i + "行，部门单位输入不能为空!");
                                            odr.Close();
                                            return false;
                                        }
                                        //部门单位
                                        equipment.department = odr[9].ToString();
                                        //在用地点
                                        equipment.useLocation = odr[10].ToString();
                                        //设备状态
                                        if (odr[11].ToString() == "")
                                        {
                                            MessageBox.Show("第" + i + "行，设备状态输入不能为空!");
                                            odr.Close();
                                            return false;
                                        }
                                        equipment.state = odr[11].ToString(); //设备状态
                                        equipment.remarks = odr[12].ToString(); //备注
                                        if (BLL.bllEquipment.AddEquipment(equipment))
                                            MessageBox.Show("设备清单添加成功!");
                                        else
                                            MessageBox.Show("设备清单添加失败!");

                                        //数据库添加操作
                                        /*进行非法值的判断
                                         * 添加数据到数据表中
                                         * 添加数据时引用事物机制，避免部分数据提交
                                         * Add(odr[1].ToString(), odr[2].ToString(), odr[3].ToString());//数据库添加操作，Add方法自己写的
                                         * */

                                    }
                                    odr.Close();
                                }
                                
                            }
                           
                        }
                    }
                }
                
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            
        }

        /// <summary>
        /// 查询表名
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static string[] GetExcelSheetNames(OleDbConnection con)
        {
            try
            {
                System.Data.DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
                var sheet = new string[dt.Rows.Count];
                for (int i = 0, j = dt.Rows.Count; i < j; i++)
                {
                    //获取的SheetName是带了$的
                    sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                }
                return sheet;
            }
            catch
            {
                return null;
            }
        }
        public static DataSet ExcelToDS(string file, BackgroundWorker worker, DoWorkEventArgs e)
        {
            //string strConn = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;HDR=NO;IMEX=1';";
            string connstring = "";
            string filetype = file.Substring(file.LastIndexOf('.') + 1);
            DataSet dt = null;
            if (filetype.ToLower() == "xlsx")
            {
                // Office 07及以上版本 不能出现多余的空格 而且分号注意  
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            }

            else //if (filetype.ToLower() == "xls")
            {
                //Office 07以下版本
                connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            }

            using (OleDbConnection conn = new OleDbConnection(connstring))
            {

                conn.Open();
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //string tableName = schemaTable.Rows[0][2].ToString().Trim();
                string tableName = "sheet1$";
                string strExcel = "";
                OleDbDataAdapter myCommand = null;

                strExcel = "select * from [" + tableName + "]";

                myCommand = new OleDbDataAdapter(strExcel, connstring);
                dt = new DataSet();
                myCommand.Fill(dt, "table1");

                conn.Close();
                return dt;

            }

        }

        private void toolStripLabelExport_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count == 0)
                return ;
            /*保存对话框*/
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "导出Excel(*.xlsx)|*.xlsx";
            //sfd.FileName = title + DateTime.Now.ToString("yyyyMMddhhmmss");

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                strFileName = sfd.FileName;
                dgv = dataGridView1;
                this.backgroundWorker1.RunWorkerAsync(); // 运行 backgroundWorker 组件 
                backgroundWorker1.WorkerReportsProgress = true;
                backgroundWorker1.WorkerSupportsCancellation = true;
                FrmRadProcessBar form = new FrmRadProcessBar(this.backgroundWorker1,"正在导出表格。。。。。");// 显示进度条窗体 
                form.ShowDialog(this);
                form.Close();
               
                //OutToExcel(sfd.FileName, dataGridView1);

                
            }
            else
            {
                return ;
            }
        }
        /// <summary>
        /// 完成进程工作之后的处理动作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                MessageBox.Show(e.Error.Message);
            }
            else if (e.Cancelled)
            {
                //dt = new System.Data.DataTable(); //如果取消重新定义datatable
            }
            else
            {
                //dataGridView1.DataSource = dt;
            }
        }

      

        //你可以在这个方法内，实现你的调用，方法等。
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            //System.Data.DataTable dt = GetDataFromExcelByCom();
            //dataGridView1.DataSource = dt;
            BackgroundWorker worker = sender as BackgroundWorker;
            
            OutToExcel(strFileName, dgv, worker,e);
            //try
            //{

            //    //生成行数据
            //    Microsoft.Office.Interop.Excel.Range range;
            //    int rowIdx = 2;//第一行为标题 实际数据从第二行开始
            //    for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
            //    {

            //        worker.ReportProgress(iRow * 100 / iRowCount);//加载进度条
            //        if (worker.CancellationPending)  // 如果用户取消则跳出处理数据代码 
            //        {
            //            e.Cancel = true;
            //            break;
            //        }

            //    }

            //}
            //catch { return; }
            //finally
            //{
            //    workbook.Close(false, oMissiong, oMissiong);
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            //    workbook = null;
            //    app.Workbooks.Close();
            //    app.Quit();
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            //    app = null;
            //}
        }
        public static bool OutToExcel(string file,DataGridView dgv, BackgroundWorker worker, DoWorkEventArgs e)
        {
            //Frm导表格进度 frmm = new Frm导表格进度();
            //frmm.ShowDialog();
            int titleColumnSpan = 0;//标题的跨列数
            string title = "永安车务段工会固定资产设备清单" + DateTime.Now.ToString("yyyyMMdd");
            int columnIndex = 1;//列索引
            //fileName = file;
            /*建立Excel对象*/
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
                return false;
            }
            try
            {
                //dataGridView1.Columns
                worker.ReportProgress(10);
                excel.Application.Workbooks.Add(true);
                //excel.Visible = isShowExcel;
                /*分析标题的跨列数*/
                foreach (DataGridViewColumn column in dgv.Columns)
                {
                    if (column.HeaderText != "自增编号")//跳过自增编号这一列
                        titleColumnSpan++;
                }
                /*合并标题单元格*/
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
                //worksheet.get_Range("A1", "C10").Merge();            
                worksheet.get_Range(worksheet.Cells[1, 1] as Range, worksheet.Cells[1, titleColumnSpan] as Range).Merge();
                /*生成标题*/
                excel.Cells[1, 1] = title;
                (excel.Cells[1, 1] as Range).HorizontalAlignment = XlHAlign.xlHAlignCenter;//标题居中
                                                                                           //生成字段名称
                worker.ReportProgress(20);
                columnIndex = 1;
                for (int i = 1; i < dgv.ColumnCount; i++)
                {
                    if (dgv.Columns[i].Visible == true)
                    {
                        excel.Cells[2, columnIndex] = dgv.Columns[i].HeaderText;
                        (excel.Cells[2, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignCenter;//字段居中
                        columnIndex++;
                    }
                }
                worker.ReportProgress(30);
                //填充数据              
                for (int i = 0; i < dgv.RowCount; i++)
                {
                    worker.ReportProgress(30+(i*70/ dgv.RowCount));
                    if (worker.CancellationPending)  // 如果用户取消则跳出处理数据代码 
                    {
                        e.Cancel = true;
                        break;
                    }
                    columnIndex = 1;
                    for (int j = 1; j < dgv.ColumnCount; j++)
                    {
                        if (dgv.Columns[j].Visible == true)
                        {
                            if (dgv[j, i].ValueType == typeof(string))
                            {
                                excel.Cells[i + 3, columnIndex] = "'" + dgv[j, i].Value.ToString();
                            }
                            else
                            {
                                excel.Cells[i + 3, columnIndex] = dgv[j, i].Value.ToString();
                            }
                            (excel.Cells[i + 3, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignLeft;//字段居中
                            columnIndex++;
                        }
                    }
                }
                //frmm.setValue(50);
                worksheet.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                
                worker.ReportProgress(100);
            }
            catch { }
            finally
            {
                excel.Quit();
                excel = null;
                GC.Collect();
                //frmm.Close();
            }
            //KillProcess("Excel");
            return true;
        }
        //你可以在这个方法内，实现你的调用，方法等。
        private void backgroundWorkerIn_DoWork(object sender, DoWorkEventArgs e)
        {
            //System.Data.DataTable dt = GetDataFromExcelByCom();
            //dataGridView1.DataSource = dt;
            BackgroundWorker worker = sender as BackgroundWorker;

            DataSet db = ExcelToDS(strFileName, worker,e);//读取
            saveExcel(db, worker, e);//保存

        }
    }
}
